﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.OracleClient;
using LateBindingApi.Core;
using Excel = NetOffice.ExcelApi;
using NetOffice.ExcelApi.Enums;
using NetOffice.VBIDEApi.Enums;

using IBS;


namespace EDEX_PurchasePricesLoader
{
    public class ExcelHelper
    {
        private String fileName { get; set; }
        private String IbsConnectionString { get; set; }

        public ExcelHelper(string fileName, string IbsConnectionString)
        {
            this.fileName = fileName;
            this.IbsConnectionString = IbsConnectionString;
        }

        public void LoadFile()
        {
            Excel.Application excelApplication;
            Excel.Workbook workBook;
            Excel.Worksheet workSheet;
            LateBindingApi.Core.Factory.Initialize();
            excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;
            workBook = excelApplication.Workbooks.Open(fileName);
            workSheet = (Excel.Worksheet)workBook.Worksheets[1];
            

            EDEX_PURCHASE_PRICE pp = new EDEX_PURCHASE_PRICE();
            

            pp.SUPIBSCODE = Int32.Parse(workSheet.Range("C1").Value.ToString());
            pp.ORDER_DATE = DateTime.Parse(workSheet.Range("C6").Value.ToString());

            var ppItems = new List<EDEX_PURCHASE_PRICE_ITEM>();
            //тестовая обработка
            for (int i = 24; i <= 39; i++)
            { 
                //добавить логику обработки строк с ценами
                EDEX_PURCHASE_PRICE_ITEM ppi = new EDEX_PURCHASE_PRICE_ITEM();
                ppi.ITEMCODE = Int64.Parse(workSheet.Range("A"+i).Value.ToString());
                ppi.PURCHASE_PRICE = Decimal.Parse(workSheet.Range("D"+i).Value.ToString());
                ppItems.Add(ppi);
            }

            pp.EDEX_PURCHASE_PRICE_ITEMS = ppItems;

            /// Подключение к IBS            
            var ibs = new IBS.IbsDataContext(this.IbsConnectionString);

            ibs.SavePurchasePrice(pp);           

            workSheet.Dispose();
            workBook.Close();
            workBook.Dispose();
            excelApplication.Quit();
            excelApplication.Dispose();
        }

       


      
    }
}
